package com.mes.service.warehouse;

import com.yy.base.common.dao.Dao;
import com.yy.base.util.StringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Service
public class SxkSjListService {
    @Autowired
    private Dao dao;


    //查询所有领料单
    public List<Map<String,Object>> selectSxListByCode() {
        String sql = "SELECT " +
                " ll.code AS CODE  " +
                "FROM " +
                " Wms_Material_List ll, " +
                " Wms_Material_List_Mx mx, " +
                " m_product wl, " +
                " m_product_category fl  " +
                "WHERE " +
                " ll.wms_material_list_id = mx.wms_material_list_id  " +
                " AND mx.m_product_id = wl.m_product_id  " +
                " AND wl.m_product_category_id = fl.m_product_category_id  " +
                " AND fl. " +
                "VALUE " +
                " = 'SC-SX'  " +
               " AND mx.use_status != 'RECEIVE'  " +
                "GROUP BY " +
                " ll.code  " ;

        return dao.query(sql);
    }

    //查询所有机台
    public List<Map<String,Object>> selectEquipmentByCode() {
        String sql = " select eam_facilities_id as EQID, code as EQCODE from eam_facilities where Facility_Type = 'FZJ' " ;

        return dao.query(sql);
    }

    //查询所有物料
    public List<Map<String,Object>> selectProductByCode() {
        String sql = " select wl.value as WLCODE ,wl.name as WLNAME, wl.m_product_id as WLID\n" +
                " from m_product_category fl ,m_product wl \n" +
                " where fl.m_product_category_id = wl.m_product_category_id\n" +
                " and fl.value = 'SC-SX' " ;

        return dao.query(sql);
    }

    //根据传过来的领料单号查询所带信息
    public List<Map<String,Object>> selectSxkPlanListByCode(String code) {
        String sql = "SELECT  " +
                "  ll.code,  " +
                " ll.wms_material_list_id as LLID, " +
                "  mx.wms_material_list_mx_id as MXID, " +
                "  wl.NAME AS WLNAME,  " +
                "  ys.NAME AS YSNAME,  " +

                "  ys.emes_color_id as COLORID,  " +
                "  WL.m_product_id AS WLID,  " +
                "  cm.emes_size_id as CMID, " +
                "  ll.aims_warehouse_id as CKID, " +
                "  ck.NAME AS CKNAME," +
                "  mx.qty AS QTY,  " +
                "  dw.NAME AS DWNAME , " +
                "  dw.c_uom_id as DWID  " +
                " FROM  " +
                "  Wms_Material_List ll  " +
                "  LEFT JOIN Wms_Material_List_Mx mx ON ll.wms_material_list_id = mx.wms_material_list_id  " +
                "  LEFT JOIN emes_color ys ON mx.emes_color_id = ys.emes_color_id  " +
                "  LEFT JOIN c_uom dw ON mx.c_uom_id = dw.c_uom_id  " +
                "  LEFT JOIN m_product wl ON wl.m_product_id = mx.m_product_id   " +
                "  LEFT JOIN emes_size cm ON mx.emes_size_id = cm.emes_size_id " +
                "  LEFT JOIN m_warehouse ck ON ll.aims_warehouse_id = ck.m_warehouse_id  " +
                " WHERE  " +
              //  "  mx.use_status = 'OK'  AND " +
                "   LL.code = ?" ;

        return dao.query(sql,code);
    }

    //查询飞织机库的物料
    public List<Map<String,Object>> selectSxkEquipment(String eqId) {
        String sql = " SELECT  " +
                "  ckwl.m_product_id as WLID,wl.c_uom_id as DWID , dw.name as DWNAME ,wl.name as WLNAME , null as QTY " +
                "FROM  " +
                "  m_warehouse ck,  " +
                "  wms_warehouse_product ckwl ,  " +
                "  m_product wl ,  " +
                "  c_uom dw  " +
                "WHERE  " +
                "  ck.m_warehouse_id = ckwl.m_warehouse_id   " +
                "  and ckwl.m_product_id = wl.m_product_id  " +
                "  and wl.c_uom_id = dw.c_uom_id  " +
                "AND ck.VALUE = 'XBK-SX' " ;

        return dao.query(sql);
    }

    //查询颜色
    public List<Map<String,Object>> selectColor() {
        String sql = " select  ys.emes_color_id as YSID, ys.name as YSNAME from emes_color  ys   " ;

        return dao.query(sql);
    }


    //根据传过来的物料ID，颜色ID，尺码ID定位库存批次表的ID
    public List<Map<String,Object>> selectStorageBatch(String WLID, String COLORID) {
        String sql = "SELECT\n" +
                "\tbatch.wms_storage_batch_id as BATCHID , sum(-kc.qty) as SUMQTYS " +
                "FROM\n" +
                "\tWms_Storage_Batch batch , wms_storage_in_out kc\n" +
                "WHERE batch.wms_storage_batch_id = kc.wms_storage_batch_id\n" +
                "and \tbatch.m_product_id = '"+WLID+"' \n" +
                "\tAND batch.emes_color_id = '"+COLORID+"'\n" +
                " and kc.opt_mode in ('领料出库','纱线库上架','纱线库下架')\t " +
                "group by \tbatch.wms_storage_batch_id  " ;


        return dao.query(sql);
    }


    //查询CREATEBY和UPDATEBY的ID值
    public List<Map<String,Object>> getEquipmentputCreateby(String userIdtime){
        return dao.query("select CREATEDBY as CREATEDBY, UPDATEDBY as UPDATEDBY from ad_user where ad_user_id = ?",userIdtime);
    }


    //保存纱线库上架信息
    @SuppressWarnings("unchecked")
    public boolean saveStorageLists(String BATCHID, String HOUSEID, double  QTYS, String UOMID, String types, String CODE, String INTESTID, String status, String adorgId, String adclientId, String createBy, String updatedBy, Date time) {
        boolean result = false;
        String uuid = StringUtil.getUUID();
        String lastRest = "Y";
        String sql =  "INSERT INTO Wms_Storage_In_Out (ISACTIVE,Wms_Storage_In_Out_ID,Wms_Storage_Batch_ID, M_Warehouse_ID,Qty,C_Uom_ID,Opt_Mode,Opt_Code,Data_Id,STATUS,AD_ORG_ID,AD_CLIENT_ID,CREATED,CREATEDBY,UPDATED,UPDATEDBY) " +
                "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        result = dao.executeSql(sql, lastRest,uuid,BATCHID,HOUSEID,QTYS,UOMID,types,CODE,INTESTID,status,adorgId,adclientId,time,createBy,time,updatedBy) > 0;

        return result;
    }


    //领料单明细修改状态
    @SuppressWarnings("unchecked")
    public boolean updateStorageMx(String id) {
        boolean result = false;

        String Status= "RECEIVE";

        String sql =  "update  wms_material_list_mx set use_status = ? where wms_material_list_mx_id = ? ";

        result = dao.executeSql(sql,Status,id) > 0;
        return result;
    }
}
